package acceso.listener;

import acceso.hibernate.AccesoHibernate;
import acceso.util.Escritor;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ContenedorLista {

    public List getCorreos()
            throws SQLException, Exception {
        List l = new ArrayList();

        Connection oConn = null;

        Statement ps = null;
        ResultSet rs = null;

        String sql = "select co.*, de.no_destin, de.em_destin, de.ti_destin  from correo.tbcorreo co, correo.tbdestin de  where co.id_correo=de.id_correo  and ti_estado='P'  and fe_correo<=current_timestamp(0)  order by co.id_correo, de.ti_destin";

        oConn = AccesoHibernate.getSessionFactory().openStatelessSession().connection();
        ps = oConn.createStatement();

        rs = ps.executeQuery(sql);

        while (rs.next()) {
            l.add(new CorreoItem(rs.getString("id_correo"), rs.getString("fe_correo"), rs.getString("no_remite"), rs.getString("em_remite"), rs.getString("no_asunto"), rs.getString("ti_estado"), rs.getString("no_mensaj"), rs.getString("no_destin"), rs.getString("em_destin"), rs.getString("ti_destin"), rs.getString("ti_conten"), rs.getString("il_adjunt"), rs.getString("co_adjunt"), rs.getString("no_adjunt"), rs.getString("il_compre"), rs.getString("no_respon"), rs.getString("em_respon")));
        }
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (oConn != null) {
                oConn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        rs = null;
        ps = null;
        oConn = null;

        return l;
    }

    public void updateCorreo(String id_correo, String ti_estado)
            throws SQLException, Exception {
        Connection oConn = null;
        Statement ps = null;

        String sql = "update correo.tbcorreo set ti_estado='" + ti_estado + "' where id_correo='" + id_correo + "'";

        oConn = AccesoHibernate.getSessionFactory().openStatelessSession().connection();
        ps = oConn.createStatement();

        Escritor.escribe_frawor("Inicio " + sql);
        ps.executeUpdate(sql);
        Escritor.escribe_frawor("Final " + sql);
        try {
            if (ps != null) {
                ps.close();
            }
            if (oConn != null) {
                oConn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        ps = null;
        oConn = null;
    }
}
